﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Data.Common;
using Ivy.Common.Framework.Base;
using Ivy.Common.Framework.DataAccess;
using Ivy.UserRegistration.Entity;


namespace Ivy.UserRegistration.DataAccess
{
    public class UserDao : IDaoInterface<User, UserCriteria>
    {
        #region Variables & Constants
        #endregion Variables & Constants

        #region Constructor & Destructor
        public UserDao() : base() { }
        
        #endregion Constructor & Destructor

        #region Public Properties
        #endregion Public Properties

        #region Public Methods
        #region IDaoInterface<User, UserCriteria> Members
        /// <summary>
        /// Created by Mrinmoy Das
        /// </summary>
        /// <returns></returns>
        public bool Insert(User userEntity)
        {
            bool IsOperationSuccessful = false;
            
                if (userEntity.EntityState == Common.Framework.Base.EntityState.New)
                {
                    DbConnection DbConn = DataAccessProviderUtility.CreateConnectionWithConnectionString();
                    DbCommand CmdObj = DataAccessProviderUtility.CreateCommand();
                    CmdObj.CommandText = "Ivy_User_Insert";
                    //CmdObj.CommandText = UserManagementSQLCommandText.InsertUser();
                    CmdObj.CommandType = CommandType.StoredProcedure;
                    //CmdObj.CommandType = CommandType.Text;


                    DataAccessProviderUtility.AddParameter(CmdObj, "@UserLoginID", ParameterDirection.Input, DbType.String, userEntity.UserLoginID);
                    DataAccessProviderUtility.AddParameter(CmdObj, "@UserPassword", ParameterDirection.Input, DbType.String, userEntity.UserPassword);
                    DataAccessProviderUtility.AddParameter(CmdObj, "@UserFirstName", ParameterDirection.Input, DbType.String, userEntity.UserFirstName);
                    DataAccessProviderUtility.AddParameter(CmdObj, "@UserLastName", ParameterDirection.Input, DbType.String, userEntity.UserLastName);
                    DataAccessProviderUtility.AddParameter(CmdObj, "@UserPrimaryEmailID", ParameterDirection.Input, DbType.String, userEntity.UserPrimaryEmailID);
                    DataAccessProviderUtility.AddParameter(CmdObj, "@UserVerificationCode", ParameterDirection.Input, DbType.String, userEntity.UserVerificationCode);
                    DataAccessProviderUtility.AddParameter(CmdObj, "@UserIsVerified", ParameterDirection.Input, DbType.Boolean, userEntity.UserIsVerified);

                    DatabaseCommon.InsertStoredProcForParameterDatEntityBase(CmdObj, userEntity);

                    DataAccessProviderUtility.AddParameter(CmdObj, "@UserKey", ParameterDirection.Output, DbType.Int32, DBNull.Value);
                    DataAccessProviderUtility.AddParameter(CmdObj, "@Sys_DateOfCreation", ParameterDirection.Output, DbType.DateTime, DBNull.Value);

                    CmdObj.Connection = DbConn;
                    DbConn.Open();
                    CmdObj.Prepare();
                    int _NoOfRecords = CmdObj.ExecuteNonQuery();
                    if (_NoOfRecords > 0)
                    {
                        userEntity.UserKey = Convert.ToInt32(CmdObj.Parameters["@UserKey"].Value);
                        userEntity.Sys_DateOfCreation = Convert.ToDateTime(CmdObj.Parameters["@Sys_DateOfCreation"].Value);
                        userEntity.Sys_DateOfModification = userEntity.Sys_DateOfCreation;
                        userEntity.EntityState = EntityState.UnChanged;
                        IsOperationSuccessful = true;
                    }
                    CmdObj.Dispose();
                    DbConn.Close();
                }
                else
                {
                    throw new Exception("For inserting new User entity data to database, [EntityState] attribute should be [New]");
                }
            
            return IsOperationSuccessful;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public bool Update(User userEntity)
        {
            bool IsOperationSuccessful = false;
            
                if (userEntity.EntityState == Common.Framework.Base.EntityState.Changed)
                {
                    DbConnection DbConn = DataAccessProviderUtility.CreateConnectionWithConnectionString();
                    DbCommand CmdObj = DataAccessProviderUtility.CreateCommand();
                    CmdObj.CommandText = "Ivy_User_Update";
                    CmdObj.CommandType = CommandType.StoredProcedure;

                    DataAccessProviderUtility.AddParameter(CmdObj, "@UserKey", ParameterDirection.Input, DbType.Int32, userEntity.UserKey);
                    DataAccessProviderUtility.AddParameter(CmdObj, "@UserLoginID", ParameterDirection.Input, DbType.String, userEntity.UserLoginID);
                    DataAccessProviderUtility.AddParameter(CmdObj, "@UserPassword", ParameterDirection.Input, DbType.String, userEntity.UserPassword);
                    DataAccessProviderUtility.AddParameter(CmdObj, "@UserFirstName", ParameterDirection.Input, DbType.String, userEntity.UserFirstName);
                    DataAccessProviderUtility.AddParameter(CmdObj, "@UserLastName", ParameterDirection.Input, DbType.String, userEntity.UserLastName);
                    DataAccessProviderUtility.AddParameter(CmdObj, "@UserPrimaryEmailID", ParameterDirection.Input, DbType.String, userEntity.UserPrimaryEmailID);
                    DataAccessProviderUtility.AddParameter(CmdObj, "@UserVerificationCode", ParameterDirection.Input, DbType.String, userEntity.UserVerificationCode);
                    DataAccessProviderUtility.AddParameter(CmdObj, "@UserIsVerified", ParameterDirection.Input, DbType.Boolean, userEntity.UserIsVerified);

                    DatabaseCommon.UpdateStoredProcForParameterDatEntityBase(CmdObj, userEntity);

                    DataAccessProviderUtility.AddParameter(CmdObj, "@Sys_DateOfModification", ParameterDirection.Output, DbType.DateTime, DBNull.Value);

                    CmdObj.Connection = DbConn;
                    DbConn.Open();
                    CmdObj.Prepare();
                    int _NoOfRecords = CmdObj.ExecuteNonQuery();
                    if (_NoOfRecords == 1)
                    {
                        userEntity.Sys_DateOfModification = Convert.ToDateTime(CmdObj.Parameters["@Sys_DateOfModification"].Value);
                        userEntity.EntityState = EntityState.UnChanged;
                        IsOperationSuccessful = true;
                    }

                    CmdObj.Dispose();
                    DbConn.Close();
                }
                else
                {
                    throw new Exception("For Updating existing User entity data to database, [EntityState] attribute should be [Changed]");
                }
            
            return IsOperationSuccessful;
        }

        public bool Delete(User userEntity)
        {
            bool IsOperationSuccessful = false;
            
                userEntity.EntityState = EntityState.Changed;
                userEntity.Sys_IsDeleted = true;
                if (Update(userEntity))
                {
                    IsOperationSuccessful = true;
                }

            return IsOperationSuccessful;
        }

        public User GetByID(string ID)
        {
            User _SelectedUser = null; //IMPORTANT NOTE:- Just declare the variable never create an instance, create instance only when required.
            
                DbConnection DbConn = DataAccessProviderUtility.CreateConnectionWithConnectionString();
                DbCommand CmdObj = DataAccessProviderUtility.CreateCommand();
                CmdObj.CommandText = "Ivy_User_GetByID";
                CmdObj.CommandType = CommandType.StoredProcedure;

                DataAccessProviderUtility.AddParameter(CmdObj, "@UserKey", ParameterDirection.Input, DbType.Int32, Convert.ToInt32(ID));
                //DataAccessProviderUtility.AddParameter(CmdObj, "@Sys_IsDeleted", ParameterDirection.Input, DbType.Boolean, this.Sys_IsDeleted);
                //DataAccessProviderUtility.AddParameter(CmdObj, "@Sys_OrganizationID", ParameterDirection.Input, DbType.Int32, this.Sys_OrganizationID);

                CmdObj.Connection = DbConn;
                DbConn.Open();
                CmdObj.Prepare();
                DbDataReader _DataReader = CmdObj.ExecuteReader();

                if (_DataReader.HasRows)
                {
                    if (_DataReader.Read())
                    {
                        _SelectedUser = new User(); //IMPORTANT NOTE:- Create instance only when required.
                        TranslateUserFieldsFromReaderToEntity(_SelectedUser, _DataReader);
                    }
                }
                else
                {
                    throw new Exception("No User found");
                }
                CmdObj.Dispose();
                DbConn.Close();
            
            return _SelectedUser;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public List<User> GetAll()
        {
            List<User> _UserList = new List<User>();
            
                DbConnection DbConn = DataAccessProviderUtility.CreateConnectionWithConnectionString();
                DbCommand CmdObj = DataAccessProviderUtility.CreateCommand();
                CmdObj.CommandText = "Ivy_User_GetAll";
                CmdObj.CommandType = CommandType.StoredProcedure;

                CmdObj.Connection = DbConn;
                DbConn.Open();
                CmdObj.Prepare();

                DbDataReader _DataReader = CmdObj.ExecuteReader();
                if (_DataReader.HasRows)
                {
                    while (_DataReader.Read())
                    {
                        User _TempUser = new User(); //IMPORTANT NOTE:- Create instance only when required.
                        this.TranslateUserFieldsFromReaderToEntity(_TempUser, _DataReader);
                        _UserList.Add(_TempUser);
                    }
                }
                CmdObj.Dispose();
                DbConn.Close();
            

            return _UserList;
        }

        #endregion

        public User GetByLoginID(string loginID)
        {
            return GetByLoginID(loginID, -1);
        }

        public User GetByLoginID(string loginID, Int32 organizationID)
        {
            User _SelectedUser = null; //IMPORTANT NOTE:- Just declare the variable never create an instance, create instance only when required.
            

                if (loginID != string.Empty)
                {

                    DbConnection dbConn = DataAccessProviderUtility.CreateConnectionWithConnectionString();
                    DbCommand CmdObj = DataAccessProviderUtility.CreateCommand();
                    CmdObj.CommandText = "Ivy_User_GetByLoginID";
                    CmdObj.CommandType = CommandType.StoredProcedure;

                    DataAccessProviderUtility.AddParameter(CmdObj, "@UserLoginID", ParameterDirection.Input, DbType.String, loginID);
                    DataAccessProviderUtility.AddParameter(CmdObj, "@Sys_OrganizationID", ParameterDirection.Input, DbType.Int32, organizationID);

                    CmdObj.Connection = dbConn;
                    dbConn.Open();
                    CmdObj.Prepare();

                    DbDataReader _DataReader = CmdObj.ExecuteReader();

                    if (_DataReader.HasRows)
                    {
                        if (_DataReader.Read())
                        {
                            _SelectedUser = new User(); //IMPORTANT NOTE:- Create instance only when required.
                            TranslateUserFieldsFromReaderToEntity(_SelectedUser, _DataReader);
                            //CopyValueFromEntityToADO(_SelectedUser);
                        }
                    }

                    CmdObj.Dispose();
                    dbConn.Close();
                }
            
            return _SelectedUser;
        }

        /*public List<User> GetAll()
        {
            throw new NotImplementedException();
        }*/

        /// <summary>
        /// Work in progress - Realest07
        /// </summary>
        /// <param name="userCriteria"></param>
        /// <returns></returns>
        public List<User> GetByCriteria(UserCriteria userCriteria)
        {
            var _UserList = new List<User>();
            // SqlDataReader _DataReader = null;  - this is needed for the commented out code

           
                DbConnection DbConn = DataAccessProviderUtility.CreateConnectionWithConnectionString();
                DbCommand CmdObj = DataAccessProviderUtility.CreateCommand();
                CmdObj.CommandText = "Ivy_User_GetByCriteria";
                CmdObj.CommandType = CommandType.StoredProcedure;

                DataAccessProviderUtility.AddParameter(CmdObj, "@UserLoginID", ParameterDirection.Input, DbType.String, userCriteria.UserLoginID);
                DataAccessProviderUtility.AddParameter(CmdObj, "@UserPassword", ParameterDirection.Input, DbType.String, userCriteria.UserPassword);
                DataAccessProviderUtility.AddParameter(CmdObj, "@UserFirstName", ParameterDirection.Input, DbType.String, userCriteria.UserFirstName);
                DataAccessProviderUtility.AddParameter(CmdObj, "@UserLastName", ParameterDirection.Input, DbType.String, userCriteria.UserLastName);
                DataAccessProviderUtility.AddParameter(CmdObj, "@UserPrimaryEmailID", ParameterDirection.Input, DbType.String, userCriteria.UserPrimaryEmailID);
                DataAccessProviderUtility.AddParameter(CmdObj, "@UserVerificationCode", ParameterDirection.Input, DbType.String, userCriteria.UserVerificationCode);
                DataAccessProviderUtility.AddParameter(CmdObj, "@UserIsVerified", ParameterDirection.Input, DbType.Boolean, userCriteria.UserIsVerified);

                //DatabaseCommon.DefaultStoredProcParameterValuesForGetByCriteria(CmdObj, userCriteria); Not sure if this method will allow the SP to return the data

                CmdObj.Connection = DbConn;
                DbConn.Open();
                CmdObj.Prepare();

                DbDataReader _DataReader = CmdObj.ExecuteReader();

                /*
                 * This code works *
                DbConnection DbConn = DataAccessProviderUtility.CreateConnectionWithConnectionString();
                DbConn.Open();

                SqlCommand CmdObj = new SqlCommand("Ivy_User_GetByCriteria", (SqlConnection)DbConn);

                CmdObj.CommandType = CommandType.StoredProcedure;

                CmdObj.Parameters.Add(new SqlParameter("@UserLoginID", userCriteria.UserLoginID));

                _DataReader = CmdObj.ExecuteReader();
                 * This code works *
                */

                if (_DataReader.HasRows)
                {
                    while (_DataReader.Read())
                    {
                        User _UserTemp = new User();
                        this.TranslateUserFieldsFromReaderToEntity(_UserTemp, _DataReader);
                        _UserList.Add(_UserTemp);
                    }

                }
                CmdObj.Dispose();
                DbConn.Dispose();
            

            return _UserList;
        }

        public List<User> GetUserBySocietyKey(int SocietyKey)
        {
            List<User> _UsersList = new List<User>();
              DbConnection DbConn = DataAccessProviderUtility.CreateConnectionWithConnectionString();
                DbCommand CmdObj = DataAccessProviderUtility.CreateCommand();
                CmdObj.CommandText = "Ivy_GetAllUsersBySocietyKey";
                CmdObj.CommandType = CommandType.StoredProcedure;

                DataAccessProviderUtility.AddParameter(CmdObj, "@UserSocietyKey", ParameterDirection.Input, DbType.Int32, SocietyKey);

                CmdObj.Connection = DbConn;
                DbConn.Open();
                CmdObj.Prepare();

                DbDataReader _DataReader = CmdObj.ExecuteReader();
                if (_DataReader.HasRows)
                {
                    while (_DataReader.Read())
                    {
                        User _TempUser = new User(); //IMPORTANT NOTE:- Create instance only when required.
                        this.TranslateUserFieldsFromReaderToEntity(_TempUser, _DataReader);
                        _UsersList.Add(_TempUser);
                    }
                }
            

            return _UsersList;
        }

        public User GetUserByEmailId(string PrimaryEmailId)
        {
            User _SelectedUser = null;
            
                DbConnection DbConn = DataAccessProviderUtility.CreateConnectionWithConnectionString();
                DbCommand CmdObj = DataAccessProviderUtility.CreateCommand();
                CmdObj.CommandText = "Ivy_User_GetByPrimaryEmailId";
                //CmdObj.CommandText = UserManagementSQLCommandText.InsertUserProfile();
                CmdObj.CommandType = CommandType.StoredProcedure;

                DataAccessProviderUtility.AddParameter(CmdObj, "@UserPrimaryEmailID", ParameterDirection.Input, DbType.String, (PrimaryEmailId));
                CmdObj.Connection = DbConn;
                DbConn.Open();
                CmdObj.Prepare();
                DbDataReader _DataReader = CmdObj.ExecuteReader();

                if (_DataReader.HasRows)
                {
                    if (_DataReader.Read())
                    {
                        _SelectedUser = new User();
                        TranslateUserFieldsFromReaderToEntity(_SelectedUser, _DataReader);
                    }
                }
            
            return _SelectedUser;

        }

        public List<UserProfileReportList> GetAllRegisterdUserProfileList()
        {
            List<UserProfileReportList> _UsersList = new List<UserProfileReportList>();
            
                DbConnection DbConn = DataAccessProviderUtility.CreateConnectionWithConnectionString();
                DbCommand CmdObj = DataAccessProviderUtility.CreateCommand();
                CmdObj.CommandText = "Dasideen_UserDetails_GetAll";
                CmdObj.CommandType = CommandType.StoredProcedure;

                CmdObj.Connection = DbConn;
                DbConn.Open();
                CmdObj.Prepare();

                DbDataReader _DataReader = CmdObj.ExecuteReader();
                if (_DataReader.HasRows)
                {
                    while (_DataReader.Read())
                    {
                        UserProfileReportList _TempUserProfileReportList = new UserProfileReportList(); //IMPORTANT NOTE:- Create instance only when required.
                        this.TranslateUsesrFieldsFromReaderToEntity(_TempUserProfileReportList, _DataReader);
                        _UsersList.Add(_TempUserProfileReportList);
                    }
                }
                CmdObj.Dispose();
                DbConn.Close();
            
            return _UsersList;
        }

        public User GetUserWithAdministratorAuthorizationByUserKey(int Userkey)
        {
            User _SelectedUser = null;
            
                DbConnection DbConn = DataAccessProviderUtility.CreateConnectionWithConnectionString();
                DbCommand CmdObj = DataAccessProviderUtility.CreateCommand();
                CmdObj.CommandText = "DasIdeen_User_GetUserWithAdministratorAuthorization";
                CmdObj.CommandType = CommandType.StoredProcedure;

                DataAccessProviderUtility.AddParameter(CmdObj, "@Userkey", ParameterDirection.Input, DbType.Int32, (Userkey));
                CmdObj.Connection = DbConn;
                DbConn.Open();
                CmdObj.Prepare();
                DbDataReader _DataReader = CmdObj.ExecuteReader();

                if (_DataReader.HasRows)
                {
                    if (_DataReader.Read())
                    {
                        _SelectedUser = new User();
                        TranslateUserFieldsFromReaderToEntity(_SelectedUser, _DataReader);
                    }
                }
           
            return _SelectedUser;
        }

        public bool IsUserHavingContentUpdateAuthorization(int UserKey, int ContentKey, string ActivityTypeCode, string ContentTypeCode)
        {
            bool IsUserAuthorized = false;
            DbConnection DbConn = DataAccessProviderUtility.CreateConnectionWithConnectionString();
            DbCommand CmdObj = DataAccessProviderUtility.CreateCommand();
            CmdObj.CommandText = "Ivy_GetUserAuthorizationByContentAndActivity";
            CmdObj.CommandType = CommandType.StoredProcedure;

            DataAccessProviderUtility.AddParameter(CmdObj, "@UserKeyAsString", ParameterDirection.Input, DbType.String, (UserKey.ToString()));
            DataAccessProviderUtility.AddParameter(CmdObj, "@ContentKey", ParameterDirection.Input, DbType.Int32, (ContentKey));
            DataAccessProviderUtility.AddParameter(CmdObj, "@ActivityTypeCode", ParameterDirection.Input, DbType.String, (ActivityTypeCode));
            DataAccessProviderUtility.AddParameter(CmdObj, "@ContentTypeCode", ParameterDirection.Input, DbType.String, (ContentTypeCode));
            CmdObj.Connection = DbConn;
            DbConn.Open();
            CmdObj.Prepare();
            DbDataReader _DataReader = CmdObj.ExecuteReader();

            if (_DataReader.HasRows)
            {
                IsUserAuthorized = true;
            }
            return IsUserAuthorized;
        }

        public UserProfileReportList GetUserByUserKey(int UserKey)
        {
            UserProfileReportList _SelectedUser = null; //IMPORTANT NOTE:- Just declare the variable never create an instance, create instance only when required.

            DbConnection DbConn = DataAccessProviderUtility.CreateConnectionWithConnectionString();
            DbCommand CmdObj = DataAccessProviderUtility.CreateCommand();
            CmdObj.CommandText = "Dasideen_User_GetUserProfileDetailsByUserKey";
            CmdObj.CommandType = CommandType.StoredProcedure;

            DataAccessProviderUtility.AddParameter(CmdObj, "@UserKey", ParameterDirection.Input, DbType.Int32, Convert.ToInt32(UserKey));
            

            CmdObj.Connection = DbConn;
            DbConn.Open();
            CmdObj.Prepare();
            DbDataReader _DataReader = CmdObj.ExecuteReader();

            if (_DataReader.HasRows)
            {
                if (_DataReader.Read())
                {
                    _SelectedUser = new UserProfileReportList(); //IMPORTANT NOTE:- Create instance only when required.
                    TranslateUsesrFieldsFromReaderToEntity(_SelectedUser, _DataReader);
                }
            }
            else
            {
                throw new Exception("No User found");
            }
            CmdObj.Dispose();
            DbConn.Close();

            return _SelectedUser;
        }
        #endregion Public Methods

        #region Events
        #endregion Events

        #region Private Methods

        /// <summary>
        /// Created by Mrinmoy Das
        /// </summary>
        /// <param name="userEntity"></param>
        /// <param name="pDbDataReader"></param>
        private void TranslateUserFieldsFromReaderToEntity(User pUser, DbDataReader pDbDataReader)
        {
            
                pUser.UserKey = Convert.ToInt32(pDbDataReader["UserKey"]);
                pUser.UserLoginID = Convert.ToString(pDbDataReader["UserLoginID"]);
                pUser.UserPassword = Convert.ToString(pDbDataReader["UserPassword"]);
                pUser.UserFirstName = Convert.ToString(pDbDataReader["UserFirstName"]);
                pUser.UserLastName = Convert.ToString(pDbDataReader["UserLastName"]);
                pUser.UserPrimaryEmailID = Convert.ToString(pDbDataReader["UserPrimaryEmailID"]);
                pUser.UserVerificationCode = Convert.ToString(pDbDataReader["UserVerificationCode"]);
                pUser.UserIsVerified = Convert.ToBoolean(pDbDataReader["UserIsVerified"]);

                DatabaseCommon.TranslateSystemFieldsFromReaderToEntity(pUser, pDbDataReader);
            
        }

        private void TranslateUsesrFieldsFromReaderToEntity(UserProfileReportList pUserProfileReportList, DbDataReader pDbDataReader)
        {
            pUserProfileReportList.UserKey = Convert.ToInt32(pDbDataReader["UserKey"]);
            pUserProfileReportList.UserLoginID = Convert.ToString(pDbDataReader["UserLoginID"]);
            pUserProfileReportList.UserFirstName = Convert.ToString(pDbDataReader["UserFirstName"]);
            pUserProfileReportList.UserLastName = Convert.ToString(pDbDataReader["UserLastName"]);

            pUserProfileReportList.UserFullName = Convert.ToString(pDbDataReader["UserFullName"]);
            pUserProfileReportList.UserSex = Convert.ToString(pDbDataReader["UserSex"]);
            pUserProfileReportList.UserPrimaryEmailID = Convert.ToString(pDbDataReader["UserPrimaryEmailID"]);
            pUserProfileReportList.UserContactNumber = Convert.ToString(pDbDataReader["UserContactNumber"]);
            pUserProfileReportList.UserAlternateEmailID = Convert.ToString(pDbDataReader["UserAlternateEmailID"]);

            pUserProfileReportList.Sys_DateOfCreation = Convert.ToDateTime(pDbDataReader["Sys_DateOfCreation"]);

            if (!pDbDataReader.IsDBNull(pDbDataReader.GetOrdinal("UserProfileImageData")))
            {
                pUserProfileReportList.UserProfileImageData = (Byte[])(pDbDataReader["UserProfileImageData"]);
            }
            else
            {
                pUserProfileReportList.UserProfileImageData = null;
            }
        }
        
        #endregion Private Methods


    }
}
